﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.IO;
using System.Net;
using System.Data.OleDb;

namespace NLBS.Utility
{
    /// <summary>
    /// from http://www.codeplex.com/sqlhost/Wiki/View.aspx?title=UploadAndExecute
    /// </summary>
   public class SqlHelper
    {
       public static void RunSQL(string fileUrl, string connectionString, int timeout)
       {
           SqlConnection conn = null;
           try
           {
               //this.Response.Write(String.Format("Opening url {0}<BR>", fileUrl));

               // read file
               WebRequest request = WebRequest.Create(fileUrl);
               using (StreamReader sr = new StreamReader(request.GetResponse().GetResponseStream()))
               {
                   //this.Response.Write("Connecting to SQL Server database...<BR>");

                   // Create new connection to database
                   conn = new SqlConnection(connectionString);

                   conn.Open();

                   while (!sr.EndOfStream)
                   {
                       StringBuilder sb = new StringBuilder();
                       SqlCommand cmd = conn.CreateCommand();

                       while (!sr.EndOfStream)
                       {
                           string s = sr.ReadLine();
                           if (s != null && s.ToUpper().Trim().Equals("GO"))
                           {
                               break;
                           }

                           sb.AppendLine(s);
                       }

                       // Execute T-SQL against the target database
                       cmd.CommandText = sb.ToString();
                       cmd.CommandTimeout = timeout;

                       cmd.ExecuteNonQuery();
                   }

               }
               //this.Response.Write("T-SQL file executed successfully");
           }
           catch (Exception ex)
           {
               //this.Response.Write(String.Format("An error occured: {0}", ex.ToString()));
               throw ex;
           }
           finally
           {
               // Close out the connection
               //
               if (conn != null)
               {
                   try
                   {
                       conn.Close();
                       conn.Dispose();
                   }
                   catch (Exception e)
                   {
                       //this.Response.Write(String.Format(@"Could not close the connection.  Error was {0}", e.ToString()));
                       throw e;
                   }
               }
           }     
       }

       public static void RunAccessSQL(string fileUrl, string connectionString, int timeout)
       {
           OleDbConnection conn = null;
           try
           {
               //this.Response.Write(String.Format("Opening url {0}<BR>", fileUrl));

               // read file
               WebRequest request = WebRequest.Create(fileUrl);
               using (StreamReader sr = new StreamReader(request.GetResponse().GetResponseStream()))
               {
                   //this.Response.Write("Connecting to SQL Server database...<BR>");

                   // Create new connection to database
                   conn = new OleDbConnection(connectionString);

                   conn.Open();

                   while (!sr.EndOfStream)
                   {
                       StringBuilder sb = new StringBuilder();
                       OleDbCommand cmd = conn.CreateCommand();

                       while (!sr.EndOfStream)
                       {
                           string s = sr.ReadLine();
                           if (s != null && s.ToUpper().Trim().Equals("GO"))
                           {
                               break;
                           }

                           sb.AppendLine(s);
                       }
                       foreach (string item in sb.ToString().Split(new char[]{';'}))
                       {
                           if (item.Trim().Length > 0)
                           {
                               // Execute T-SQL against the target database
                               cmd.CommandText = item;
                               cmd.CommandTimeout = timeout;

                               cmd.ExecuteNonQuery();
                           }
                       }
                      
                   }

               }
               //this.Response.Write("T-SQL file executed successfully");
           }
           catch (Exception ex)
           {
               //this.Response.Write(String.Format("An error occured: {0}", ex.ToString()));
               throw ex;
           }
           finally
           {
               // Close out the connection
               //
               if (conn != null)
               {
                   try
                   {
                       conn.Close();
                       conn.Dispose();
                   }
                   catch (Exception e)
                   {
                       //this.Response.Write(String.Format(@"Could not close the connection.  Error was {0}", e.ToString()));
                       throw e;
                   }
               }
           }
       }
    }
}
